﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class RecordDAL
    {
        public List<DeviceRecord> QueryDeviceRecord(DeviceRecord deviceRecord)
        {
            string strSql = "SELECT * FROM vi_device_record";
            string sqlwhere = string.Empty;

            if (deviceRecord.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceRecord.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceRecord.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceRecord.DeviceNO + "%' AND";
            }
            if (deviceRecord.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + deviceRecord.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.StartTime))
            {
                sqlwhere += " UpdateTime>='" + deviceRecord.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.EndTime))
            {
                sqlwhere += " UpdateTime <='" + deviceRecord.EndTime + "' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY UpdateTime DESC";

            if (deviceRecord.Recordhead > -1 && deviceRecord.Recordtail > 0)
            {
                strSql += " LIMIT " + deviceRecord.Recordhead + "," + deviceRecord.Recordtail;
            }

            return SqlHelper<DeviceRecord>.SqlList(strSql);
        }

        public int QueryDeviceRecordCount(DeviceRecord deviceRecord)
        {
            string strSql = "SELECT Count(1) FROM vi_device_record";
            string sqlwhere = string.Empty;

            if (deviceRecord.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceRecord.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceRecord.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceRecord.DeviceNO + "%' AND";
            }
            if (deviceRecord.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + deviceRecord.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.StartTime))
            {
                sqlwhere += " UpdateTime>='" + deviceRecord.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(deviceRecord.EndTime))
            {
                sqlwhere += " UpdateTime <='" + deviceRecord.EndTime + "' AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<DeviceRecord>.SqlScalar(strSql);
        }

        public bool DeleteDeviceRecord(int id)
        {
            string strSql = "UPDATE device_record SET IsDelete=1 WHERE ID=" + id;
            return SqlHelper<DeviceRecord>.SqlExecute(strSql);
        }

        public List<LadderRecord> QueryLadderRecord(LadderRecord ladderRecord)
        {
            string strSql = "SELECT * FROM vi_ladder_record";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(ladderRecord.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + ladderRecord.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + ladderRecord.DeviceNO + "%' AND";
            }
            if (ladderRecord.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + ladderRecord.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.StartTime))
            {
                sqlwhere += " UpdateTime>='" + ladderRecord.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.EndTime))
            {
                sqlwhere += " DATE(UpdateTime) <=DATE('" + ladderRecord.EndTime + "') AND";
            }
            strSql += " ORDER BY UpdateTime DESC";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (ladderRecord.Recordhead > -1 && ladderRecord.Recordtail > 0)
            {
                strSql += " LIMIT " + ladderRecord.Recordhead + "," + ladderRecord.Recordtail;
            }

            return SqlHelper<LadderRecord>.SqlList(strSql);
        }

        public int QueryLadderRecordCount(LadderRecord ladderRecord)
        {
            string strSql = "SELECT COUNT(1) FROM vi_ladder_record";
            string sqlwhere = string.Empty;

            if (!string.IsNullOrEmpty(ladderRecord.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + ladderRecord.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + ladderRecord.DeviceNO + "%' AND";
            }
            if (ladderRecord.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + ladderRecord.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.StartTime))
            {
                sqlwhere += " UpdateTime>='" + ladderRecord.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(ladderRecord.EndTime))
            {
                sqlwhere += " DATE(UpdateTime) <=DATE('" + ladderRecord.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<LadderRecord>.SqlScalar(strSql);
        }

        public bool DeleteLadderRecord(int id)
        {
            string strSql = "UPDATE ladder_records SET IsDelete =1 WHERE ID=" + id;
            return SqlHelper<LadderRecord>.SqlExecute(strSql);
        }

        public bool InsertLadderRecord(string deviceuuid, string owneruuid)
        {
            string strSql = string.Format("INSERT INTO ladder_records(DeviceID,OwnerID,UpdateTime)VALUES((SELECT ID FROM device_info WHERE DeviceUuid='{0}' AND IsDelete=0 AND DeviceState='在线' LIMIT 0,1),(SELECT ID FROM owner_info WHERE UUID='{1}' LIMIT 0,1),'{2}')", deviceuuid, owneruuid, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            return SqlHelper<LadderRecord>.SqlExecute(strSql);
        }
        public bool InsertDeviceRecord(string deviceuuid, string deviceno, string deviceip, string result)
        {
            string strSql = string.Format(@"INSERT into device_record (DeviceID,State,UpdateTime) VALUES((SELECT ID FROM device_info WHERE DeviceUuid='{0}' AND DeviceNO='{1}' AND DeviceIP='{2}' AND IsDelete=0),'{3}','{4}');UPDATE device_info SET DeviceState='{3}' WHERE DeviceUuid='{0}' AND DeviceNO='{1}' AND DeviceIP='{2}' AND IsDelete=0", deviceuuid, deviceno, deviceip, result, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            //string strSql = string.Format("INSERT into device_record (DeviceID,State,UpdateTime) VALUES((SELECT ID FROM device_info WHERE DeviceNO='{0}' AND IsDelete=0),'{1}','{2}');UPDATE device_info SET DeviceState='{1}' WHERE DeviceNO='{0}' AND IsDelete=0", deviceid, result, DateTime.Now.ToString());
            return SqlHelper<DeviceRecord>.SqlExecute(strSql);
        }

        public bool TimedDeleteDeviceRecoed(int day)
        {
            string strSql = "UPDATE device_record SET IsDelete=1 WHERE DATE(UpdateTime)<DATE('" + DateTime.Now.AddDays(-day).ToString() + "')";
            return SqlHelper<DeviceRecord>.SqlExecute(strSql);
        }

        public bool TimedDeleteLadderRecoed(int day)
        {
            string strSql = "UPDATE ladder_records SET IsDelete=1 WHERE DATE(UpdateTime)<DATE('" + DateTime.Now.AddDays(-day).ToString() + "')";
            return SqlHelper<LadderRecord>.SqlExecute(strSql);
        }

        public List<WorkModeStateRecord> QueryWorkModeStateRecord(WorkModeStateRecord workModeState)
        {
            string strSql = "SELECT * FROM vi_workmodestate_record";
            string sqlwhere = string.Empty;

            if (workModeState.projectID > 0)
            {
                sqlwhere += " projectID=" + workModeState.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(workModeState.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + workModeState.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(workModeState.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + workModeState.DeviceNO + "%' AND";
            }
            if (workModeState.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + workModeState.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(workModeState.StartTime))
            {
                sqlwhere += " WorkModeUpdateTime>='" + workModeState.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(workModeState.EndTime))
            {
                sqlwhere += " DATE(WorkModeUpdateTime) <=DATE('" + workModeState.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY WorkModeUpdateTime DESC";

            if (workModeState.Recordhead > -1 && workModeState.Recordtail > 0)
            {
                strSql += " LIMIT " + workModeState.Recordhead + "," + workModeState.Recordtail;
            }

            return SqlHelper<WorkModeStateRecord>.SqlList(strSql);
        }

        public int QueryWorkModeStateRecordCount(WorkModeStateRecord workModeState)
        {
            string strSql = "SELECT Count(1) FROM vi_workmodestate_record";
            string sqlwhere = string.Empty;

            if (workModeState.projectID > 0)
            {
                sqlwhere += " projectID=" + workModeState.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(workModeState.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + workModeState.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(workModeState.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + workModeState.DeviceNO + "%' AND";
            }
            if (workModeState.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + workModeState.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(workModeState.StartTime))
            {
                sqlwhere += " WorkModeUpdateTime>='" + workModeState.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(workModeState.EndTime))
            {
                sqlwhere += " DATE(WorkModeUpdateTime) <=DATE('" + workModeState.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<WorkModeStateRecord>.SqlScalar(strSql);
        }

        public List<DeviceControllerStateRecord> QueryControllerStateRecord(DeviceControllerStateRecord deviceControllerState)
        {
            string strSql = "SELECT * FROM vi_controllerstate_record";
            string sqlwhere = string.Empty;

            if (deviceControllerState.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceControllerState.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceControllerState.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceControllerState.DeviceNO + "%' AND";
            }
            if (deviceControllerState.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + deviceControllerState.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.StartTime))
            {
                sqlwhere += " ControllerStateUpdateTime>='" + deviceControllerState.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.EndTime))
            {
                sqlwhere += " DATE(ControllerStateUpdateTime) <=DATE('" + deviceControllerState.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            strSql += " ORDER BY ControllerStateUpdateTime DESC";

            if (deviceControllerState.Recordhead > -1 && deviceControllerState.Recordtail > 0)
            {
                strSql += " LIMIT " + deviceControllerState.Recordhead + "," + deviceControllerState.Recordtail;
            }

            return SqlHelper<DeviceControllerStateRecord>.SqlList(strSql);
        }

        public int QueryControllerStateRecordCount(DeviceControllerStateRecord deviceControllerState)
        {
            string strSql = "SELECT Count(1) FROM vi_controllerstate_record";
            string sqlwhere = string.Empty;

            if (deviceControllerState.projectID > 0)
            {
                sqlwhere += " projectID=" + deviceControllerState.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.DeviceName))
            {
                sqlwhere += " DeviceName like '%" + deviceControllerState.DeviceName + "%' AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.DeviceNO))
            {
                sqlwhere += " DeviceNO like '%" + deviceControllerState.DeviceNO + "%' AND";
            }
            if (deviceControllerState.DeviceID > 0)
            {
                sqlwhere += " DeviceID=" + deviceControllerState.DeviceID + " AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.StartTime))
            {
                sqlwhere += " ControllerStateUpdateTime>='" + deviceControllerState.StartTime + "' AND";
            }
            if (!string.IsNullOrEmpty(deviceControllerState.EndTime))
            {
                sqlwhere += " DATE(ControllerStateUpdateTime) <=DATE('" + deviceControllerState.EndTime + "') AND";
            }

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<DeviceControllerStateRecord>.SqlScalar(strSql);
        }

        public bool DeleteLadderRecord(string deviceuuid)
        {
            string strSql = string.Format("DELETE FROM ladder_records WHERE DeviceID=(SELECT ID FROM device_info WHERE DeviceUuid='{0}' AND IsDelete=0 LIMIT 0,1) AND UpdateTime<'{1}'", deviceuuid, DateTime.Now.AddMonths(-1).ToString());
            return SqlHelper<LadderRecord>.SqlDelete(strSql);
        }

        public bool AddAuditInfo(QRCodeAuditInfo qRCodeAuditInfo, int projectid)
        {
            string strsql = string.Format(@"INSERT INTO auditrecord (Name,ownerAddress,phone,applicationTime,projectId,auditid) VALUES ('{0}', '{1}', '{2}', '{3}',{4},{5})", qRCodeAuditInfo.ownerName, qRCodeAuditInfo.ownerAddress, qRCodeAuditInfo.phone, Convert.ToDateTime(qRCodeAuditInfo.applicationTime).ToString("yyyy-MM-dd HH:mm:ss"), projectid, qRCodeAuditInfo.id);
            return SqlHelper<QRCodeAuditInfoData>.SqlExecute(strsql);
        }

        public bool UpdateAuditInfo(QRCodeAuditInfo qRCodeAuditInfo)
        {
            string strsql = string.Format(@"UPDATE auditrecord SET Name='{0}',ownerAddress='{1}',phone='{2}',auditStatus=0,syncFlag=0 WHERE auditid={3}", qRCodeAuditInfo.ownerName, qRCodeAuditInfo.ownerAddress, qRCodeAuditInfo.phone, qRCodeAuditInfo.id);
            return SqlHelper<QRCodeAuditInfoData>.SqlExecute(strsql);
        }

        public int QueryProjectID(string projectkey)
        {
            string strSql = "SELECT ID FROM project_info WHERE project_key='" + projectkey + "'  AND IsDelete=0";
            return SqlHelper<QRCodeAuditInfoData>.SqlScalar(strSql);
        }

        public int QueryAuditID(int auditid)
        {
            string strSql = "SELECT ID FROM auditrecord WHERE auditid=" + auditid;
            return SqlHelper<QRCodeAuditInfoData>.SqlScalar(strSql);
        }
    }
}
